package com.nekonomics
package persistence.query

import domain.detail.SkuDetail

import cats.data.*
import doobie.*
import doobie.implicits.*
import doobie.postgres.implicits.*
import zio.*

trait SkuDetailRepo {

  def listPageSortedByTime(pageNum: Int, pageSize: Int): ConnectionIO[List[SkuDetail]]

  def findSkuDetailById(skuId: Long): ConnectionIO[Option[SkuDetail]]

  def listSkuDetails(skuIds: List[Long]): ConnectionIO[List[SkuDetail]]
  
  def listByMerchantId(merchantId: Long): ConnectionIO[List[SkuDetail]]
  
}

object SkuDetailRepo {
  val live: ULayer[SkuDetailRepo] = ZLayer.succeed(SkuDetailRepoLive)
}


object SkuDetailRepoLive extends  SkuDetailRepo {

  private val sqlTemp =
    sql"""
      select
         sku.id, sku.spu_id, spu.name, spu.description,
         sku.image_url,
         sku.price, sku.stock, sku.is_active,
         sku.created_at, sku.updated_at, sku.is_deleted
      from  public.sku
      join public.spu on sku.spu_id = spu.id
    """



  override def listSkuDetails(skuIds: List[Long]): doobie.ConnectionIO[List[SkuDetail]] = {
    val inFr = Fragments.in(fr"sku.id", NonEmptyList.fromListUnsafe(skuIds))
    (sqlTemp ++ fr"where" ++ inFr)
      .query[SkuDetail]
      .to[List]
  }

  override def listPageSortedByTime(pageNum: Int, pageSize: Int): ConnectionIO[List[SkuDetail]] = {
    (sqlTemp ++
    sql"""
      where sku.is_deleted = false
        and sku.is_active = true
      order by sku.updated_at desc
      limit $pageSize offset ${pageSize * (pageNum - 1)}
     """)
      .query[SkuDetail]
      .to[List]
  }


  override def findSkuDetailById(skuId: Long): ConnectionIO[Option[SkuDetail]] =
    listSkuDetails(List(skuId))
      .map(_.headOption)


  override def listByMerchantId(merchantId: Long): doobie.ConnectionIO[List[SkuDetail]] = {
    (sqlTemp ++ fr"where spu.merchant_id = $merchantId")
      .query[SkuDetail]
      .to[List]
  }
}